/**
 * 
 */
package com.wordpress.sharebyblog.world.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;

import com.wordpress.sharebyblog.world.domain.City;
import com.wordpress.sharebyblog.world.domain.Country;

/**
 * @author Bharat Mehta
 *
 */
public class CountryDaoImpl extends NamedParameterJdbcDaoSupport implements CountryDao {
	
	private static final String SQL_FIND_COUNTRY_CAPITAL = "select a.code,a.name,a.continent,a.region,a.surfacearea,a.indepyear,a.population,a.lifeexpectancy,a.gnp,a.gnpold,a.localname,a.governmentform,a.headofstate,a.code2,b.id,b.name,b.countrycode,b.district,b.population from country a ,city b where a.code=:code and a.capital = b.id";
	
	private static final String SQL_LIST_COUNTRIES = "select a.code,a.name,a.continent,a.region,a.surfacearea,a.indepyear,a.population,a.lifeexpectancy,a.gnp,a.gnpold,a.localname,a.governmentform,a.headofstate,a.code2,b.id,b.name,b.countrycode,b.district,b.population from country a ,city b where  a.capital = b.id";
	
	
	private static final String SQL_LIST_REGIONS = "select distinct region from country";
	
	private static final String SQL_LIST_COUNTRIES_BY_CONTINENT = SQL_LIST_COUNTRIES + " and a.continent =:continent";

	
	@Override
	public List<String> findRegions() {
		Map<String,Object> map = new HashMap<String,Object>();
		return getNamedParameterJdbcTemplate().query(SQL_LIST_REGIONS
				, map, new BeanPropertyRowMapper<String>(String.class));
	}
	
	
	@Override
	public Country findByCountryCode(String countryCode) {
		Map<String,Object> map = new HashMap<String,Object>();
		map.put("code", countryCode);
		return getNamedParameterJdbcTemplate().queryForObject(SQL_FIND_COUNTRY_CAPITAL
				, map, getCountryCapitalRowMapper());
	}
	
	@Override
	public List<Country> listCountries() {
		return getNamedParameterJdbcTemplate().query(SQL_LIST_COUNTRIES,new HashMap<String,Object>(),
				 getCountryCapitalRowMapper());
	}


	private RowMapper<Country> getCountryCapitalRowMapper() {
		return new RowMapper<Country>() {

			@Override
			public Country mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Country country = new Country();
				country.setCode(rs.getString("code"));
				country.setName(rs.getString("name"));
				country.setContinent(rs.getString("continent"));
				country.setRegion(rs.getString("region"));
				country.setLifeExpectancy(rs.getFloat("lifeexpectancy"));
				country.setSurfaceArea(rs.getBigDecimal("surfacearea"));
				country.setIndepYear(rs.getInt("indepyear"));
				country.setPopulation(rs.getBigDecimal("population"));
				country.setGnp(rs.getBigDecimal("gnp"));
				country.setGnpOld(rs.getBigDecimal("gnpold"));
				country.setLocalName(rs.getString("localname"));
				country.setGovernmentForm(rs.getString("governmentform"));
				country.setHeadOfState(rs.getString("headofstate"));
				country.setCode2(rs.getString("code2"));
				City capital = new City();
				capital.setId(rs.getLong("id"));
				capital.setName(rs.getString("b.name"));
				capital.setCountryCode(rs.getString("countrycode"));
				capital.setPopulation(rs.getBigDecimal("b.population"));
				capital.setDistrict(rs.getString("district"));
				country.setCapital(capital);
				
				 
				return country;
			}
		};
	}


	@Override
	public List<Country> findCountriesByContinent(String continent) {
		Map<String,Object> map = new HashMap<String,Object>();
		map.put("continent", continent);
		return getNamedParameterJdbcTemplate().query(SQL_LIST_COUNTRIES_BY_CONTINENT
				, map, getCountryCapitalRowMapper());
	}

}
